package com.hshh.controller;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.json.JSONException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.servlet.ModelAndView;

import com.hshh.common.Constant;
import com.hshh.service.CompanyUploadService;

@Controller
public class CompanyForExcelController {
	@Autowired
	CompanyUploadService companyUploadService;
	
	//파일정보추출(공통)
	@RequestMapping(value="/fileInfo.file")
	public void getFileDownload(HttpServletRequest request,
			HttpServletResponse response) throws IOException {
		System.out.println("here");
		// TODO Auto-generated method stub
		MultipartHttpServletRequest mhsr = (MultipartHttpServletRequest) request;
		Map<String, MultipartFile> files = mhsr.getFileMap();
		
		Set<String> set = files.keySet();
		Iterator<String> iterator =  set.iterator();
		MultipartFile file = null;
		
		List<Object> list = new ArrayList<Object>();
		List<Object> excelList = new ArrayList<Object>();
		
		while(iterator.hasNext()) {
			Map<String, Object> map = new HashMap<String, Object>();
			String key = iterator.next();
			file = files.get(key);
			List<Object> exclList = getExcelByJson(request, file);
			map.put("sizeB", file.getSize());
			map.put("sizeKB", (double) file.getSize() / 1024);
			map.put("sizeMB", (double) file.getSize() / 1048576);
			map.put("fieldName", file.getName());
			map.put("fileName", file.getOriginalFilename());
			list.add(map);
			
			excelList.add(exclList);
		}
		
		org.json.JSONObject json = new org.json.JSONObject();
		try {
			json.put("file", list);
			json.put("excel", excelList);
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		response.setContentType("text/html");
		response.setCharacterEncoding("UTF-8");
		response.getWriter().write(json.toString());
	}
	
	//회사등록페이지(view)
	@RequestMapping("/companyUpload")
	public String companyUpload(){
		return "companyUpload";
	}
	
	//회사등록 엑셀 저장(json)
	@RequestMapping(value="/storeCompanyForExcel.json")
	public ModelAndView storeCompanyForExcel(HttpServletRequest request, HttpServletResponse response){
		ModelAndView mav = new ModelAndView();
		//JSON DATA FORMAT
		//json:{root:[{...},{...}..]}
		try{
			org.json.JSONObject jsonData = new org.json.JSONObject(request.getParameter("json").toString());
			Map<?, ?> jsonMap = jsonData.getMap();
			
			org.json.JSONArray jsonArray = new org.json.JSONArray(jsonMap.get("root").toString());
			
			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
			Map<String, Object> rowHashMap = null;
			
			for (int i = 0; i < jsonArray.length(); i++) {
				rowHashMap = (Map<String, Object>) jsonArray.getJSONObject(i).getMap();
				list.add(i, rowHashMap);
			}
			
			list = companyUploadService.insertCompanies(list);
			mav.addObject("excel", list);
		} catch(Exception e){
			e.printStackTrace();
		}
		mav.setViewName(Constant.URL_JSONVIEW);
		return mav;
	}
	

	//회사등록페이지(view)
	@RequestMapping("/measurementUpload")
	public String measurementUpload(){
		return "measurementUpload";
	}
	
	//회사등록 엑셀 저장(json)
	@RequestMapping(value="/storeMeansurementForExcel.json")
	public ModelAndView storeMeansurementForExcel(HttpServletRequest request, HttpServletResponse response){
		ModelAndView mav = new ModelAndView();
		//JSON DATA FORMAT
		//json:{root:[{...},{...}..]}
		try{
			org.json.JSONObject jsonData = new org.json.JSONObject(request.getParameter("json").toString());
			Map<?, ?> jsonMap = jsonData.getMap();
			
			org.json.JSONArray jsonArray = new org.json.JSONArray(jsonMap.get("root").toString());
			
			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
			Map<String, Object> rowHashMap = null;
			
			for (int i = 0; i < jsonArray.length(); i++) {
				rowHashMap = (Map<String, Object>) jsonArray.getJSONObject(i).getMap();
				list.add(i, rowHashMap);
			}
			
			list = companyUploadService.insertCompanies(list);
			mav.addObject("excel", list);
		} catch(Exception e){
			e.printStackTrace();
		}
		mav.setViewName(Constant.URL_JSONVIEW);
		return mav;
	}
	
	//엑셀 추출함수(공통)
	@SuppressWarnings({ "deprecation", "unused" })
	private List<Object> getExcelByJson(HttpServletRequest request, MultipartFile file) throws IOException {
		List<String> fields = (List<String>) request.getAttribute("fields");

		POIFSFileSystem fileSystem = new POIFSFileSystem(file.getInputStream());
		HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);

		HSSFSheet sheet = workbook.getSheetAt(0); // 첫번째 쉬트
		List<Object> list = new ArrayList<Object>();
		Hashtable<String, String> ht = null;

		int rows = sheet.getPhysicalNumberOfRows(); // 행개수
		for (int i = 1; i < rows; i++) {
			ht = new Hashtable<String, String>();

			HSSFRow row = sheet.getRow(i); // row 가져오기
			if (row != null) {
				int cells = row.getLastCellNum(); // 셀 개수 가져오기
					for (short c = 0; c < cells; c++) {
						HSSFCell cell = row.getCell(c);
						if (cell != null) {
							String value = null;
							switch (cell.getCellType()) { // 각셀의 데이터값을 가져올때 맞는데이터형으로 변환한다.
								case HSSFCell.CELL_TYPE_FORMULA:
									value = cell.getCellFormula();
									break;
								case HSSFCell.CELL_TYPE_NUMERIC:
									value = "" + cell.getNumericCellValue();
									break;
								case HSSFCell.CELL_TYPE_STRING:
									value = "" + cell.getStringCellValue();
									break;
								case HSSFCell.CELL_TYPE_BLANK:
									value = "";
									break;
								case HSSFCell.CELL_TYPE_ERROR:
									value = "" + cell.getErrorCellValue();
									break;
								default:
									value = "";
							}
							// hashtable 에값을 넣어 둔다.
							String checkStr = value.replaceAll(" ", "");
							
							if("".equals(checkStr)) {
								value = "";
							}
							if(fields != null) {
								ht.put(fields.get(cell.getCellNum()), value);
							}
							else {
								ht.put("excel_" + cell.getCellNum(), value);
							}
						} else {
							int cellNum = 99;
							String cellName = "default";
							if(cell != null) {
								cellNum = cell.getCellNum();
								if(fields != null) {
									cellName = fields.get(cellNum);
								}
							}
							if(fields != null) {
								ht.put(cellName, "");
							}
							else {
								ht.put("excel_" + cellNum, "");
							}
							
						}
					}
			}
			//추출한 row 중 유효한 데이터가 2개 이상일때만 출력
			if(ht.size() > 1){
				Iterator<String> iterator = ht.keySet().iterator();
				boolean flag = false;
				int notEmptyCount = 0;
				while(iterator.hasNext()) {
					String key = iterator.next();
					if(!"".equals(ht.get(key))) {
						notEmptyCount++;
						if(notEmptyCount > 1){
							flag = true;
							break;
						}
					}
				}
				if(flag) {
					list.add(ht);
				}
			}
		}
		return list;
	}
}
